CREATE PROCEDURE dbo.asi_ConvertWorkPayments
@batchKey uniqueidentifier,
@componentKey uniqueidentifier
AS
/* Convert a batch of WorkPayments to a batch of Payments in preparation
   for posting */
DECLARE @workPaymentKey                        uniqueidentifier
DECLARE @amount                                decimal(18,4)
DECLARE @paymentApplicationRestrictionCode    int
DECLARE @financialEntityKey                    uniqueidentifier
DECLARE @paymentDate                        datetime
DECLARE @amountHome                            decimal(18,4)
DECLARE @currencyCode                        nchar(3)
DECLARE @originalPaymentKey                    uniqueidentifier
DECLARE @payorContactKey                    uniqueidentifier
DECLARE @summaryDisplay                        nvarchar(50)
DECLARE @accessKey                            uniqueidentifier
DECLARE @createdByUserKey                    uniqueidentifier
DECLARE @createdOn                            datetime
DECLARE @updatedByUserKey                    uniqueidentifier
DECLARE @updatedOn                            datetime
DECLARE @systemEntityKey                    uniqueidentifier
DECLARE @ownerGroupKey                        uniqueidentifier
DECLARE @paymentMethodKey                    uniqueidentifier
DECLARE @paymentReferenceData                nvarchar(50)
DECLARE @paymentDetailKey                    uniqueidentifier
DECLARE @markedForDeleteOn                    datetime
DECLARE @originatingBatchKey                uniqueidentifier
DECLARE @workMonetaryApplicationKey            uniqueidentifier
DECLARE @wmaAmount                            decimal(18,4)
DECLARE @invoiceNumber                        nvarchar(50)
DECLARE @orderNumber                        nvarchar(50)
DECLARE @invoiceLineNumber                    int
DECLARE @invoiceKey                            uniqueidentifier
DECLARE @srcWorkInvoiceLineKey                uniqueidentifier
DECLARE @srcPaymentKey                        uniqueidentifier
DECLARE @discountTaken                        decimal(18,4)
DECLARE @currencyVariance                    decimal(18,4)
DECLARE @invoiceDistributionKey                uniqueidentifier
DECLARE @paymentScheduleLineKey                uniqueidentifier
DECLARE @orderLineNumber                    int
DECLARE @transactionDate                    datetime
DECLARE @transactionType                    int
DECLARE @wmaMarkedForDeleteOn                datetime
DECLARE @wmaBatchKey                        uniqueidentifier

DECLARE @lastWorkPaymentKey                    uniqueidentifier
DECLARE @lastWMAKey                            uniqueidentifier
DECLARE @newItemKey                            uniqueidentifier

SET @lastWMAKey    = NEWID()
SET @lastWorkPaymentKey = NEWID()

DECLARE WorkPaymentData CURSOR FAST_FORWARD FOR
SELECT wp.WorkPaymentKey, wp.Amount, wp.PaymentApplicationRestrictionCode, wp.FinancialEntityKey,
    wp.PaymentDate, wp.AmountHome, wp.CurrencyCode, wp.OriginalPaymentKey, wp.PayorContactKey,
    wp.SummaryDisplay, wp.AccessKey, wp.CreatedByUserKey, wp.CreatedOn, wp.UpdatedByUserKey, wp.UpdatedOn,
    wp.SystemEntityKey, wp.OwnerGroupKey, wp.PaymentMethodKey, wp.PaymentReferenceData, wp.PaymentDetailKey, wp.MarkedForDeleteOn, wp.OriginatingBatchKey,
    wma.WorkMonetaryApplicationKey, wma.Amount, wma.InvoiceNumber, wma.OrderNumber, wma.InvoiceLineNumber, wma.InvoiceKey,
    wma.SrcWorkInvoiceLineKey, wma.SrcPaymentKey, wma.DiscountTaken, wma.CurrencyVariance,
    wma.InvoiceDistributionKey, wma.PaymentScheduleLineKey, wma.OrderLineNumber, wma.TransactionDate, wma.TransactionType,
    wma.MarkedForDeleteOn, wma.BatchKey
FROM WorkPayment wp
LEFT OUTER JOIN WorkMonetaryApplication wma on wp.WorkPaymentKey = wma.SrcWorkPaymentKey
WHERE wp.BatchKey = @batchKey
ORDER BY wp.WorkPaymentKey, wma.WorkMonetaryApplicationKey

OPEN WorkPaymentData
FETCH NEXT FROM WorkPaymentData into @workPaymentKey, @amount, @paymentApplicationRestrictionCode,
    @financialEntityKey, @paymentDate, @amountHome, @currencyCode, @originalPaymentKey, @payorContactKey,
    @summaryDisplay, @accessKey, @createdByUserKey, @createdOn, @updatedByUserKey, @updatedOn, @systemEntityKey, @ownerGroupKey,
    @paymentMethodKey, @paymentReferenceData, @paymentDetailKey, @markedForDeleteOn, @originatingBatchKey,    @workMonetaryApplicationKey, @wmaAmount,
    @invoiceNumber, @orderNumber, @invoiceLineNumber, @invoiceKey, @srcWorkInvoiceLineKey, @srcPaymentKey,
    @discountTaken, @currencyVariance, @invoiceDistributionKey, @paymentScheduleLineKey, @orderLineNumber, @transactionDate,
    @transactionType, @wmaMarkedForDeleteOn, @wmaBatchKey
    
WHILE @@FETCH_STATUS = 0
BEGIN
      IF @workPaymentKey != @lastWorkPaymentKey
      BEGIN
            SET @newItemKey = NEWID()
            INSERT INTO UniformRegistry(UniformKey, ComponentKey)
                VALUES (@newItemKey, @componentKey)
            INSERT INTO PaymentMain(PaymentKey, Amount, PaymentApplicationRestrictionCode, FinancialEntityKey,
                                PaymentDate, AmountHome, CurrencyCode, OriginalPaymentKey, PayorContactKey,
                                SummaryDisplay, AccessKey, CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn,
                                SystemEntityKey, OwnerGroupKey, PaymentMethodKey, PaymentReferenceData, PaymentDetailKey,
                                MarkedForDeleteOn, FinalBatchKey, OriginatingBatchKey)
                VALUES(@newItemKey, @amount, @paymentApplicationRestrictionCode, @financialEntityKey, @paymentDate,
                                @amountHome, @currencyCode, @originalPaymentKey, @payorContactKey, @summaryDisplay,
                                @accessKey, @createdByUserKey, @createdOn, @updatedByUserKey, @updatedOn, @systemEntityKey,
                                @ownerGroupKey, @paymentMethodKey, @paymentReferenceData, @paymentDetailKey,
                                @markedForDeleteOn, @batchKey, @originatingBatchKey )
      END
        
      IF @workMonetaryApplicationKey is not null AND @workMonetaryApplicationKey != @lastWMAKey
      BEGIN
      --NB no InvoiceLineNumber in MonetaryApplication, although there is one for Work MA.
      INSERT INTO MonetaryApplication(MonetaryApplicationKey, Amount, InvoiceNumber, OrderNumber, InvoiceKey,
        InvoiceLineKey, SrcPaymentKey, DiscountTaken, CurrencyVariance,
        InvoiceDistributionKey, PaymentScheduleLineKey, OrderLineNumber, TransactionDate, TransactionType,
        MarkedForDeleteOn, BatchKey)
            VALUES(NEWID(), @wmaAmount,
                @invoiceNumber, @orderNumber, @invoiceKey, @srcWorkInvoiceLineKey, @newItemKey,
                @discountTaken, @currencyVariance, @invoiceDistributionKey, @paymentScheduleLineKey, @orderLineNumber, @transactionDate,
                @transactionType, @wmaMarkedForDeleteOn, @batchKey)
        SET @lastWMAKey = @workMonetaryApplicationKey
      END
      
      SET @lastWorkPaymentKey = @workPaymentKey
      
      FETCH NEXT FROM WorkPaymentData into @workPaymentKey, @amount, @paymentApplicationRestrictionCode,
            @financialEntityKey, @paymentDate, @amountHome, @currencyCode, @originalPaymentKey, @payorContactKey,
            @summaryDisplay, @accessKey, @createdByUserKey, @createdOn, @updatedByUserKey, @updatedOn, @systemEntityKey, @ownerGroupKey,
            @paymentMethodKey, @paymentReferenceData, @paymentDetailKey, @markedForDeleteOn, @originatingBatchKey,    @workMonetaryApplicationKey, @wmaAmount,
            @invoiceNumber, @orderNumber, @invoiceLineNumber, @invoiceKey, @srcWorkInvoiceLineKey, @srcPaymentKey,
            @discountTaken, @currencyVariance, @invoiceDistributionKey, @paymentScheduleLineKey, @orderLineNumber, @transactionDate,
            @transactionType, @wmaMarkedForDeleteOn, @wmaBatchKey
END
CLOSE WorkPaymentData
DEALLOCATE WorkPaymentData

GO